﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Data;
using Automobile.service.Model;

namespace Automobile.service.DAL
{
   public  class StockDal
    {
         string str = "Data Source=.;Initial Catalog=qichefuwu;Integrated Security=True";

        /// <summary>
         /// 显示商品库存信息
        /// </summary>
        /// <returns></returns>
         public List<StockModel> Stock()
        {
            string sql = "select Stock_Id,Stock_num,Stock_warn,WareHose_Id,Product_Id from Stock";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, str);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<StockModel> list = new List<StockModel>();
            foreach (DataRow item in table.Rows)
            {
                StockModel s = new StockModel();
                s.Stock_Id = Convert.ToInt32(item["Stock_Id"]);
                s.Stock_num = Convert.ToInt32(item["Stock_num"]);
                s.Stock_warn = Convert.ToInt32(item["Stock_warn"]);
                s.WareHose_Id = Convert.ToInt32(item["WareHose_Id"]);
                s.Product_Id = Convert.ToInt32(item["Product_Id"]);
                list.Add(s);
            }
            return list;
        }
        /// <summary>
         /// 添加商品库存信息
        /// </summary>
        /// <returns></returns>
         public int AddStock(StockModel s)
        {
            string sql = "insert into Stock(Stock_num,Stock_warn,WareHose_Id,Product_Id) values('"+s.Stock_num+"','"+s.Stock_warn+"','"+s.WareHose_Id+"','"+s.Product_Id+"')";
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            int i = comm.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        /// <summary>
        /// 删除商品库存信息
        /// </summary>
        /// <returns></returns>
         public int DeleStock(int Stock_Id)
        {
            string sql = "delete from Stock where Stock_Id=" + Stock_Id;
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            int i = comm.ExecuteNonQuery();
            conn.Close();
            return i;
        }

        /// <summary>
        /// 取单个商品库存信息
        /// </summary>
        /// <returns></returns>
         public DataTable GetStock(string Stock_Id)
        {
            string sql = "select Stock_Id,Stock_num,Stock_warn,WareHose_Id,Product_Id from Stock where Stock_Id=" + Stock_Id;

            SqlConnection conn = new SqlConnection(str);
            conn.Open();//打开数据库

            SqlCommand comm = new SqlCommand(sql, conn);
            DataTable table = new DataTable();
            SqlDataAdapter ada = new SqlDataAdapter(comm);
            ada.Fill(table);
            conn.Close();
            return table;
        }

        /// <summary>
        /// 更新商品库存信息
        /// </summary>
        /// <param name="stu"></param>
        /// <returns></returns>
         public int UpdateStock(StockModel s)
        {
            string sql = "update Stock set Stock_num='" + s.Stock_num + "',Stock_warn='" + s.Stock_warn + "',WareHose_Id='" + s.WareHose_Id + "',Product_Id='" + s.Product_Id + "' where Stock_Id=" + s.Stock_Id;
            SqlConnection conn = new SqlConnection(str);
            conn.Open();//打开数据库

            SqlCommand comm = new SqlCommand(sql, conn);
            //执行命令
            int count = comm.ExecuteNonQuery();
            conn.Close();

            return count;
        }
    
    }
}
